{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# From Pandas-wan to Pandas-master\n",
    "\n",
    "Pandas’ core internals, performances and code styles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "def log_head(df, head_count=10):\n",
    "    print(df.head(head_count))\n",
    "    return df\n",
    "\n",
    "def log_columns(df):\n",
    "    print(df.columns)\n",
    "    return df\n",
    "\n",
    "def log_shape(df):\n",
    "    print(f'shape = {df.shape}')\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   country  year   sex          age  suicides_no  population  \\\n",
      "0  Albania  1987  male  15-24 years           21      312900   \n",
      "\n",
      "   suicides/100k pop country-year  HDI for year  gdp_for_year ($)   \\\n",
      "0               6.71  Albania1987           NaN      2,156,624,900   \n",
      "\n",
      "   gdp_per_capita ($)    generation  \n",
      "0                 796  Generation X  \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>suicides_no</th>\n",
       "      <th>population</th>\n",
       "      <th>suicides_per_100k</th>\n",
       "      <th>country_year</th>\n",
       "      <th>HDI for year</th>\n",
       "      <th>gdp_year</th>\n",
       "      <th>gdp_capita</th>\n",
       "      <th>generation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>1987</td>\n",
       "      <td>male</td>\n",
       "      <td>15-24 years</td>\n",
       "      <td>21</td>\n",
       "      <td>312900</td>\n",
       "      <td>6.71</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Generation X</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>1987</td>\n",
       "      <td>male</td>\n",
       "      <td>35-54 years</td>\n",
       "      <td>16</td>\n",
       "      <td>308000</td>\n",
       "      <td>5.19</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Silent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Albania</td>\n",
       "      <td>1987</td>\n",
       "      <td>female</td>\n",
       "      <td>15-24 years</td>\n",
       "      <td>14</td>\n",
       "      <td>289700</td>\n",
       "      <td>4.83</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Generation X</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Albania</td>\n",
       "      <td>1987</td>\n",
       "      <td>male</td>\n",
       "      <td>75+ years</td>\n",
       "      <td>1</td>\n",
       "      <td>21800</td>\n",
       "      <td>4.59</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>G.I. Generation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Albania</td>\n",
       "      <td>1987</td>\n",
       "      <td>male</td>\n",
       "      <td>25-34 years</td>\n",
       "      <td>9</td>\n",
       "      <td>274300</td>\n",
       "      <td>3.28</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Boomers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Albania</td>\n",
       "      <td>1987</td>\n",
       "      <td>female</td>\n",
       "      <td>75+ years</td>\n",
       "      <td>1</td>\n",
       "      <td>35600</td>\n",
       "      <td>2.81</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>G.I. Generation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Albania</td>\n",
       "      <td>1987</td>\n",
       "      <td>female</td>\n",
       "      <td>35-54 years</td>\n",
       "      <td>6</td>\n",
       "      <td>278800</td>\n",
       "      <td>2.15</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Silent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Albania</td>\n",
       "      <td>1987</td>\n",
       "      <td>female</td>\n",
       "      <td>25-34 years</td>\n",
       "      <td>4</td>\n",
       "      <td>257200</td>\n",
       "      <td>1.56</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Boomers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Albania</td>\n",
       "      <td>1987</td>\n",
       "      <td>male</td>\n",
       "      <td>55-74 years</td>\n",
       "      <td>1</td>\n",
       "      <td>137500</td>\n",
       "      <td>0.73</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>G.I. Generation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Albania</td>\n",
       "      <td>1987</td>\n",
       "      <td>female</td>\n",
       "      <td>5-14 years</td>\n",
       "      <td>0</td>\n",
       "      <td>311000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Generation X</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   country  year     sex          age  suicides_no  population  \\\n",
       "0  Albania  1987    male  15-24 years           21      312900   \n",
       "1  Albania  1987    male  35-54 years           16      308000   \n",
       "2  Albania  1987  female  15-24 years           14      289700   \n",
       "3  Albania  1987    male    75+ years            1       21800   \n",
       "4  Albania  1987    male  25-34 years            9      274300   \n",
       "5  Albania  1987  female    75+ years            1       35600   \n",
       "6  Albania  1987  female  35-54 years            6      278800   \n",
       "7  Albania  1987  female  25-34 years            4      257200   \n",
       "8  Albania  1987    male  55-74 years            1      137500   \n",
       "9  Albania  1987  female   5-14 years            0      311000   \n",
       "\n",
       "   suicides_per_100k country_year  HDI for year    gdp_year  gdp_capita  \\\n",
       "0               6.71  Albania1987           NaN  2156624900         796   \n",
       "1               5.19  Albania1987           NaN  2156624900         796   \n",
       "2               4.83  Albania1987           NaN  2156624900         796   \n",
       "3               4.59  Albania1987           NaN  2156624900         796   \n",
       "4               3.28  Albania1987           NaN  2156624900         796   \n",
       "5               2.81  Albania1987           NaN  2156624900         796   \n",
       "6               2.15  Albania1987           NaN  2156624900         796   \n",
       "7               1.56  Albania1987           NaN  2156624900         796   \n",
       "8               0.73  Albania1987           NaN  2156624900         796   \n",
       "9               0.00  Albania1987           NaN  2156624900         796   \n",
       "\n",
       "        generation  \n",
       "0     Generation X  \n",
       "1           Silent  \n",
       "2     Generation X  \n",
       "3  G.I. Generation  \n",
       "4          Boomers  \n",
       "5  G.I. Generation  \n",
       "6           Silent  \n",
       "7          Boomers  \n",
       "8  G.I. Generation  \n",
       "9     Generation X  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_path = './data'\n",
    "df = (pd.read_csv(filepath_or_buffer=os.path.join(data_path, 'master.csv'))\n",
    "      .pipe(log_head, head_count=1)\n",
    "      .rename(columns={'suicides/100k pop' : 'suicides_per_100k',\n",
    "                       ' gdp_for_year ($) ' : 'gdp_year', \n",
    "                       'gdp_per_capita ($)' : 'gdp_capita',\n",
    "                       'country-year' : 'country_year'})\n",
    "      .assign(gdp_year=lambda _df: _df['gdp_year'].str.replace(',','').astype(np.int64))\n",
    "     )\n",
    "\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['country', 'year', 'sex', 'age', 'suicides_no', 'population',\n",
       "       'suicides_per_100k', 'country_year', 'HDI for year', 'gdp_year',\n",
       "       'gdp_capita', 'generation'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(27820, 12)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "101"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['country'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Albania', 'Antigua and Barbuda', 'Argentina', 'Armenia', 'Aruba',\n",
       "       'Australia', 'Austria', 'Azerbaijan', 'Bahamas', 'Bahrain'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['country'].unique()[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Generation X', 'Silent', 'G.I. Generation', 'Boomers',\n",
       "       'Millenials', 'Generation Z'], dtype=object)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['generation'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>suicides_no</th>\n",
       "      <th>population</th>\n",
       "      <th>suicides_per_100k</th>\n",
       "      <th>country_year</th>\n",
       "      <th>HDI for year</th>\n",
       "      <th>gdp_year</th>\n",
       "      <th>gdp_capita</th>\n",
       "      <th>generation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>27820</td>\n",
       "      <td>27820.000000</td>\n",
       "      <td>27820</td>\n",
       "      <td>27820</td>\n",
       "      <td>27820.000000</td>\n",
       "      <td>2.782000e+04</td>\n",
       "      <td>27820.000000</td>\n",
       "      <td>27820</td>\n",
       "      <td>8364.000000</td>\n",
       "      <td>2.782000e+04</td>\n",
       "      <td>27820.000000</td>\n",
       "      <td>27820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>101</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2321</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>Netherlands</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>25-34 years</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ireland2003</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Generation X</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>382</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13910</td>\n",
       "      <td>4642</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2001.258375</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>242.574407</td>\n",
       "      <td>1.844794e+06</td>\n",
       "      <td>12.816097</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.776601</td>\n",
       "      <td>4.455810e+11</td>\n",
       "      <td>16866.464414</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>NaN</td>\n",
       "      <td>8.469055</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>902.047917</td>\n",
       "      <td>3.911779e+06</td>\n",
       "      <td>18.961511</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.093367</td>\n",
       "      <td>1.453610e+12</td>\n",
       "      <td>18887.576472</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1985.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.780000e+02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.483000</td>\n",
       "      <td>4.691962e+07</td>\n",
       "      <td>251.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1995.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>9.749850e+04</td>\n",
       "      <td>0.920000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.713000</td>\n",
       "      <td>8.985353e+09</td>\n",
       "      <td>3447.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2002.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>4.301500e+05</td>\n",
       "      <td>5.990000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.779000</td>\n",
       "      <td>4.811469e+10</td>\n",
       "      <td>9372.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2008.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>131.000000</td>\n",
       "      <td>1.486143e+06</td>\n",
       "      <td>16.620000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.855000</td>\n",
       "      <td>2.602024e+11</td>\n",
       "      <td>24874.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2016.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22338.000000</td>\n",
       "      <td>4.380521e+07</td>\n",
       "      <td>224.970000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.944000</td>\n",
       "      <td>1.812071e+13</td>\n",
       "      <td>126352.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            country          year     sex          age   suicides_no  \\\n",
       "count         27820  27820.000000   27820        27820  27820.000000   \n",
       "unique          101           NaN       2            6           NaN   \n",
       "top     Netherlands           NaN  female  25-34 years           NaN   \n",
       "freq            382           NaN   13910         4642           NaN   \n",
       "mean            NaN   2001.258375     NaN          NaN    242.574407   \n",
       "std             NaN      8.469055     NaN          NaN    902.047917   \n",
       "min             NaN   1985.000000     NaN          NaN      0.000000   \n",
       "25%             NaN   1995.000000     NaN          NaN      3.000000   \n",
       "50%             NaN   2002.000000     NaN          NaN     25.000000   \n",
       "75%             NaN   2008.000000     NaN          NaN    131.000000   \n",
       "max             NaN   2016.000000     NaN          NaN  22338.000000   \n",
       "\n",
       "          population  suicides_per_100k country_year  HDI for year  \\\n",
       "count   2.782000e+04       27820.000000        27820   8364.000000   \n",
       "unique           NaN                NaN         2321           NaN   \n",
       "top              NaN                NaN  Ireland2003           NaN   \n",
       "freq             NaN                NaN           12           NaN   \n",
       "mean    1.844794e+06          12.816097          NaN      0.776601   \n",
       "std     3.911779e+06          18.961511          NaN      0.093367   \n",
       "min     2.780000e+02           0.000000          NaN      0.483000   \n",
       "25%     9.749850e+04           0.920000          NaN      0.713000   \n",
       "50%     4.301500e+05           5.990000          NaN      0.779000   \n",
       "75%     1.486143e+06          16.620000          NaN      0.855000   \n",
       "max     4.380521e+07         224.970000          NaN      0.944000   \n",
       "\n",
       "            gdp_year     gdp_capita    generation  \n",
       "count   2.782000e+04   27820.000000         27820  \n",
       "unique           NaN            NaN             6  \n",
       "top              NaN            NaN  Generation X  \n",
       "freq             NaN            NaN          6408  \n",
       "mean    4.455810e+11   16866.464414           NaN  \n",
       "std     1.453610e+12   18887.576472           NaN  \n",
       "min     4.691962e+07     251.000000           NaN  \n",
       "25%     8.985353e+09    3447.000000           NaN  \n",
       "50%     4.811469e+10    9372.000000           NaN  \n",
       "75%     2.602024e+11   24874.000000           NaN  \n",
       "max     1.812071e+13  126352.000000           NaN  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe(include='all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "country               object\n",
       "year                   int64\n",
       "sex                   object\n",
       "age                   object\n",
       "suicides_no            int64\n",
       "population             int64\n",
       "suicides_per_100k    float64\n",
       "country_year          object\n",
       "HDI for year         float64\n",
       "gdp_year               int64\n",
       "gdp_capita             int64\n",
       "generation            object\n",
       "dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Memory optimization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "def mem_usage(df):\n",
    "    return f'{df.memory_usage(deep=True).sum() / 1024 ** 2 : 3.2f} MB'\n",
    "\n",
    "def convert_df(df):\n",
    "    return df.astype({\n",
    "        col: 'category' for col in df.columns \n",
    "        if df[col].nunique() / df[col].shape[0] < 0.5})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " 10.28 MB\n",
      " 5.00 MB\n",
      " 1.40 MB\n",
      " 1.40 MB\n"
     ]
    }
   ],
   "source": [
    "# memory optimization\n",
    "print(mem_usage(df))\n",
    "print(mem_usage(df.set_index(['country', 'year', 'sex', 'age'])))\n",
    "print(mem_usage(convert_df(df)))\n",
    "print(mem_usage(convert_df(df.set_index(['country', 'year', 'sex', 'age']))))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Indexing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 40.7 ms, sys: 2.57 ms, total: 43.2 ms\n",
      "Wall time: 43.3 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "mi_df = df.set_index(['country', 'year', 'sex', 'age']).sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>suicides_no</th>\n",
       "      <th>population</th>\n",
       "      <th>suicides_per_100k</th>\n",
       "      <th>country_year</th>\n",
       "      <th>HDI for year</th>\n",
       "      <th>gdp_year</th>\n",
       "      <th>gdp_capita</th>\n",
       "      <th>generation</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"10\" valign=\"top\">Albania</th>\n",
       "      <th rowspan=\"10\" valign=\"top\">1987</th>\n",
       "      <th rowspan=\"6\" valign=\"top\">female</th>\n",
       "      <th>15-24 years</th>\n",
       "      <td>14</td>\n",
       "      <td>289700</td>\n",
       "      <td>4.83</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Generation X</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25-34 years</th>\n",
       "      <td>4</td>\n",
       "      <td>257200</td>\n",
       "      <td>1.56</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Boomers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35-54 years</th>\n",
       "      <td>6</td>\n",
       "      <td>278800</td>\n",
       "      <td>2.15</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Silent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5-14 years</th>\n",
       "      <td>0</td>\n",
       "      <td>311000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Generation X</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55-74 years</th>\n",
       "      <td>0</td>\n",
       "      <td>144600</td>\n",
       "      <td>0.00</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>G.I. Generation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75+ years</th>\n",
       "      <td>1</td>\n",
       "      <td>35600</td>\n",
       "      <td>2.81</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>G.I. Generation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">male</th>\n",
       "      <th>15-24 years</th>\n",
       "      <td>21</td>\n",
       "      <td>312900</td>\n",
       "      <td>6.71</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Generation X</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25-34 years</th>\n",
       "      <td>9</td>\n",
       "      <td>274300</td>\n",
       "      <td>3.28</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Boomers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35-54 years</th>\n",
       "      <td>16</td>\n",
       "      <td>308000</td>\n",
       "      <td>5.19</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Silent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5-14 years</th>\n",
       "      <td>0</td>\n",
       "      <td>338200</td>\n",
       "      <td>0.00</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Generation X</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 suicides_no  population  suicides_per_100k  \\\n",
       "country year sex    age                                                       \n",
       "Albania 1987 female 15-24 years           14      289700               4.83   \n",
       "                    25-34 years            4      257200               1.56   \n",
       "                    35-54 years            6      278800               2.15   \n",
       "                    5-14 years             0      311000               0.00   \n",
       "                    55-74 years            0      144600               0.00   \n",
       "                    75+ years              1       35600               2.81   \n",
       "             male   15-24 years           21      312900               6.71   \n",
       "                    25-34 years            9      274300               3.28   \n",
       "                    35-54 years           16      308000               5.19   \n",
       "                    5-14 years             0      338200               0.00   \n",
       "\n",
       "                                country_year  HDI for year    gdp_year  \\\n",
       "country year sex    age                                                  \n",
       "Albania 1987 female 15-24 years  Albania1987           NaN  2156624900   \n",
       "                    25-34 years  Albania1987           NaN  2156624900   \n",
       "                    35-54 years  Albania1987           NaN  2156624900   \n",
       "                    5-14 years   Albania1987           NaN  2156624900   \n",
       "                    55-74 years  Albania1987           NaN  2156624900   \n",
       "                    75+ years    Albania1987           NaN  2156624900   \n",
       "             male   15-24 years  Albania1987           NaN  2156624900   \n",
       "                    25-34 years  Albania1987           NaN  2156624900   \n",
       "                    35-54 years  Albania1987           NaN  2156624900   \n",
       "                    5-14 years   Albania1987           NaN  2156624900   \n",
       "\n",
       "                                 gdp_capita       generation  \n",
       "country year sex    age                                       \n",
       "Albania 1987 female 15-24 years         796     Generation X  \n",
       "                    25-34 years         796          Boomers  \n",
       "                    35-54 years         796           Silent  \n",
       "                    5-14 years          796     Generation X  \n",
       "                    55-74 years         796  G.I. Generation  \n",
       "                    75+ years           796  G.I. Generation  \n",
       "             male   15-24 years         796     Generation X  \n",
       "                    25-34 years         796          Boomers  \n",
       "                    35-54 years         796           Silent  \n",
       "                    5-14 years          796     Generation X  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mi_df.query('country == \"Albania\" & year == 1987').head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 5.45 ms, sys: 865 µs, total: 6.32 ms\n",
      "Wall time: 5.53 ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>suicides_no</th>\n",
       "      <th>population</th>\n",
       "      <th>suicides_per_100k</th>\n",
       "      <th>country_year</th>\n",
       "      <th>HDI for year</th>\n",
       "      <th>gdp_year</th>\n",
       "      <th>gdp_capita</th>\n",
       "      <th>generation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Albania</td>\n",
       "      <td>1987</td>\n",
       "      <td>male</td>\n",
       "      <td>25-34 years</td>\n",
       "      <td>9</td>\n",
       "      <td>274300</td>\n",
       "      <td>3.28</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Boomers</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   country  year   sex          age  suicides_no  population  \\\n",
       "4  Albania  1987  male  25-34 years            9      274300   \n",
       "\n",
       "   suicides_per_100k country_year  HDI for year    gdp_year  gdp_capita  \\\n",
       "4               3.28  Albania1987           NaN  2156624900         796   \n",
       "\n",
       "  generation  \n",
       "4    Boomers  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "df.query('country == \"Albania\" and year == 1987 and sex == \"male\" and age == \"25-34 years\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2.54 ms, sys: 1.12 ms, total: 3.66 ms\n",
      "Wall time: 3.08 ms\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "suicides_no                    9\n",
       "population                274300\n",
       "suicides_per_100k           3.28\n",
       "country_year         Albania1987\n",
       "HDI for year                 NaN\n",
       "gdp_year              2156624900\n",
       "gdp_capita                   796\n",
       "generation               Boomers\n",
       "Name: (Albania, 1987, male, 25-34 years), dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "mi_df.loc['Albania', 1987, 'male', '25-34 years']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 8.84 ms, sys: 1.24 ms, total: 10.1 ms\n",
      "Wall time: 9.17 ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>suicides_no</th>\n",
       "      <th>population</th>\n",
       "      <th>suicides_per_100k</th>\n",
       "      <th>country_year</th>\n",
       "      <th>HDI for year</th>\n",
       "      <th>gdp_year</th>\n",
       "      <th>gdp_capita</th>\n",
       "      <th>generation</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Albania</th>\n",
       "      <th>1987</th>\n",
       "      <th>male</th>\n",
       "      <th>25-34 years</th>\n",
       "      <td>9</td>\n",
       "      <td>274300</td>\n",
       "      <td>3.28</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Boomers</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               suicides_no  population  suicides_per_100k  \\\n",
       "country year sex  age                                                       \n",
       "Albania 1987 male 25-34 years            9      274300               3.28   \n",
       "\n",
       "                              country_year  HDI for year    gdp_year  \\\n",
       "country year sex  age                                                  \n",
       "Albania 1987 male 25-34 years  Albania1987           NaN  2156624900   \n",
       "\n",
       "                               gdp_capita generation  \n",
       "country year sex  age                                 \n",
       "Albania 1987 male 25-34 years         796    Boomers  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "mi_df.query('country == \"Albania\" and year == 1987 and sex == \"male\" and age == \"25-34 years\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 33.5 ms, sys: 1.53 ms, total: 35 ms\n",
      "Wall time: 34.3 ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>suicides_no</th>\n",
       "      <th>population</th>\n",
       "      <th>suicides_per_100k</th>\n",
       "      <th>country_year</th>\n",
       "      <th>HDI for year</th>\n",
       "      <th>gdp_year</th>\n",
       "      <th>gdp_capita</th>\n",
       "      <th>generation</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"10\" valign=\"top\">Albania</th>\n",
       "      <th rowspan=\"10\" valign=\"top\">1987</th>\n",
       "      <th rowspan=\"6\" valign=\"top\">female</th>\n",
       "      <th>15-24 years</th>\n",
       "      <td>14</td>\n",
       "      <td>289700</td>\n",
       "      <td>4.83</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Generation X</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25-34 years</th>\n",
       "      <td>4</td>\n",
       "      <td>257200</td>\n",
       "      <td>1.56</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Boomers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35-54 years</th>\n",
       "      <td>6</td>\n",
       "      <td>278800</td>\n",
       "      <td>2.15</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Silent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5-14 years</th>\n",
       "      <td>0</td>\n",
       "      <td>311000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Generation X</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55-74 years</th>\n",
       "      <td>0</td>\n",
       "      <td>144600</td>\n",
       "      <td>0.00</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>G.I. Generation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75+ years</th>\n",
       "      <td>1</td>\n",
       "      <td>35600</td>\n",
       "      <td>2.81</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>G.I. Generation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">male</th>\n",
       "      <th>15-24 years</th>\n",
       "      <td>21</td>\n",
       "      <td>312900</td>\n",
       "      <td>6.71</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Generation X</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25-34 years</th>\n",
       "      <td>9</td>\n",
       "      <td>274300</td>\n",
       "      <td>3.28</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Boomers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35-54 years</th>\n",
       "      <td>16</td>\n",
       "      <td>308000</td>\n",
       "      <td>5.19</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Silent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5-14 years</th>\n",
       "      <td>0</td>\n",
       "      <td>338200</td>\n",
       "      <td>0.00</td>\n",
       "      <td>Albania1987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156624900</td>\n",
       "      <td>796</td>\n",
       "      <td>Generation X</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 suicides_no  population  suicides_per_100k  \\\n",
       "country year sex    age                                                       \n",
       "Albania 1987 female 15-24 years           14      289700               4.83   \n",
       "                    25-34 years            4      257200               1.56   \n",
       "                    35-54 years            6      278800               2.15   \n",
       "                    5-14 years             0      311000               0.00   \n",
       "                    55-74 years            0      144600               0.00   \n",
       "                    75+ years              1       35600               2.81   \n",
       "             male   15-24 years           21      312900               6.71   \n",
       "                    25-34 years            9      274300               3.28   \n",
       "                    35-54 years           16      308000               5.19   \n",
       "                    5-14 years             0      338200               0.00   \n",
       "\n",
       "                                country_year  HDI for year    gdp_year  \\\n",
       "country year sex    age                                                  \n",
       "Albania 1987 female 15-24 years  Albania1987           NaN  2156624900   \n",
       "                    25-34 years  Albania1987           NaN  2156624900   \n",
       "                    35-54 years  Albania1987           NaN  2156624900   \n",
       "                    5-14 years   Albania1987           NaN  2156624900   \n",
       "                    55-74 years  Albania1987           NaN  2156624900   \n",
       "                    75+ years    Albania1987           NaN  2156624900   \n",
       "             male   15-24 years  Albania1987           NaN  2156624900   \n",
       "                    25-34 years  Albania1987           NaN  2156624900   \n",
       "                    35-54 years  Albania1987           NaN  2156624900   \n",
       "                    5-14 years   Albania1987           NaN  2156624900   \n",
       "\n",
       "                                 gdp_capita       generation  \n",
       "country year sex    age                                       \n",
       "Albania 1987 female 15-24 years         796     Generation X  \n",
       "                    25-34 years         796          Boomers  \n",
       "                    35-54 years         796           Silent  \n",
       "                    5-14 years          796     Generation X  \n",
       "                    55-74 years         796  G.I. Generation  \n",
       "                    75+ years           796  G.I. Generation  \n",
       "             male   15-24 years         796     Generation X  \n",
       "                    25-34 years         796          Boomers  \n",
       "                    35-54 years         796           Silent  \n",
       "                    5-14 years          796     Generation X  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "mi_df.sort_index().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.index.is_monotonic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mi_df.index.is_monotonic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b\n",
       "a  0  0\n",
       "a  1  1"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Indexes are not necessarily unique keys\n",
    "(pd.DataFrame({'a':range(2), 'b': range(2)}, index=['a', 'a'])\n",
    " .loc['a']\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Method chaining"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 9.82 ms, sys: 1.65 ms, total: 11.5 ms\n",
      "Wall time: 10.3 ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>unique_generation</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>age</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15-24 years</th>\n",
       "      <td>[Generation X, Millenials]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25-34 years</th>\n",
       "      <td>[Boomers, Generation X, Millenials]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35-54 years</th>\n",
       "      <td>[Silent, Boomers, Generation X]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5-14 years</th>\n",
       "      <td>[Generation X, Millenials, Generation Z]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55-74 years</th>\n",
       "      <td>[G.I. Generation, Silent, Boomers]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75+ years</th>\n",
       "      <td>[G.I. Generation, Silent]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    unique_generation\n",
       "age                                                  \n",
       "15-24 years                [Generation X, Millenials]\n",
       "25-34 years       [Boomers, Generation X, Millenials]\n",
       "35-54 years           [Silent, Boomers, Generation X]\n",
       "5-14 years   [Generation X, Millenials, Generation Z]\n",
       "55-74 years        [G.I. Generation, Silent, Boomers]\n",
       "75+ years                   [G.I. Generation, Silent]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "# Validation of generations\n",
    "(df\n",
    " .groupby('age')\n",
    " .agg({'generation':'unique'})\n",
    " .rename(columns={'generation':'unique_generation'})\n",
    "#  Recommended from v0.25\n",
    "#  .agg(unique_generation=('generation': 'unique'))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 9.53 ms, sys: 1.18 ms, total: 10.7 ms\n",
      "Wall time: 9.76 ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>unique_generation</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>age</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15-24 years</th>\n",
       "      <td>[Generation X, Millenials]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25-34 years</th>\n",
       "      <td>[Boomers, Generation X, Millenials]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35-54 years</th>\n",
       "      <td>[Silent, Boomers, Generation X]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5-14 years</th>\n",
       "      <td>[Generation X, Millenials, Generation Z]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55-74 years</th>\n",
       "      <td>[G.I. Generation, Silent, Boomers]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75+ years</th>\n",
       "      <td>[G.I. Generation, Silent]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    unique_generation\n",
       "age                                                  \n",
       "15-24 years                [Generation X, Millenials]\n",
       "25-34 years       [Boomers, Generation X, Millenials]\n",
       "35-54 years           [Silent, Boomers, Generation X]\n",
       "5-14 years   [Generation X, Millenials, Generation Z]\n",
       "55-74 years        [G.I. Generation, Silent, Boomers]\n",
       "75+ years                   [G.I. Generation, Silent]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "# Validation of generations\n",
    "(mi_df\n",
    " .groupby('age')\n",
    " .agg({'generation':'unique'})\n",
    " .rename(columns={'generation':'unique_generation'})\n",
    "#  Recommended from v0.25\n",
    "#  .agg(unique_generation=('generation': 'unique'))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "scrolled": false,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 5.24 ms, sys: 892 µs, total: 6.13 ms\n",
      "Wall time: 5.46 ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>suicides_sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Lithuania</th>\n",
       "      <th>1995</th>\n",
       "      <td>639.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996</th>\n",
       "      <td>595.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hungary</th>\n",
       "      <th>1991</th>\n",
       "      <td>575.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lithuania</th>\n",
       "      <th>2000</th>\n",
       "      <td>571.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hungary</th>\n",
       "      <th>1992</th>\n",
       "      <td>570.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lithuania</th>\n",
       "      <th>2001</th>\n",
       "      <td>568.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Russian Federation</th>\n",
       "      <th>1994</th>\n",
       "      <td>567.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Lithuania</th>\n",
       "      <th>1998</th>\n",
       "      <td>566.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1997</th>\n",
       "      <td>565.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1999</th>\n",
       "      <td>561.53</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         suicides_sum\n",
       "country            year              \n",
       "Lithuania          1995        639.30\n",
       "                   1996        595.61\n",
       "Hungary            1991        575.00\n",
       "Lithuania          2000        571.80\n",
       "Hungary            1992        570.26\n",
       "Lithuania          2001        568.98\n",
       "Russian Federation 1994        567.64\n",
       "Lithuania          1998        566.36\n",
       "                   1997        565.44\n",
       "                   1999        561.53"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "(df\n",
    " .groupby(['country', 'year'])\n",
    " .agg({'suicides_per_100k': 'sum'})\n",
    " .rename(columns={'suicides_per_100k':'suicides_sum'})\n",
    "#  Recommended from v0.25\n",
    "#  .agg(suicides_sum=('suicides_per_100k': 'sum'))\n",
    " .sort_values('suicides_sum', ascending=False)\n",
    " .head(10)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": false,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 7.54 ms, sys: 1.41 ms, total: 8.95 ms\n",
      "Wall time: 7.97 ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>suicides_sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Lithuania</th>\n",
       "      <th>1995</th>\n",
       "      <td>639.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996</th>\n",
       "      <td>595.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hungary</th>\n",
       "      <th>1991</th>\n",
       "      <td>575.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lithuania</th>\n",
       "      <th>2000</th>\n",
       "      <td>571.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hungary</th>\n",
       "      <th>1992</th>\n",
       "      <td>570.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lithuania</th>\n",
       "      <th>2001</th>\n",
       "      <td>568.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Russian Federation</th>\n",
       "      <th>1994</th>\n",
       "      <td>567.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Lithuania</th>\n",
       "      <th>1998</th>\n",
       "      <td>566.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1997</th>\n",
       "      <td>565.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1999</th>\n",
       "      <td>561.53</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         suicides_sum\n",
       "country            year              \n",
       "Lithuania          1995        639.30\n",
       "                   1996        595.61\n",
       "Hungary            1991        575.00\n",
       "Lithuania          2000        571.80\n",
       "Hungary            1992        570.26\n",
       "Lithuania          2001        568.98\n",
       "Russian Federation 1994        567.64\n",
       "Lithuania          1998        566.36\n",
       "                   1997        565.44\n",
       "                   1999        561.53"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "(df\n",
    " .groupby(['country', 'year'])\n",
    " .agg({'suicides_per_100k': 'sum'})\n",
    " .rename(columns={'suicides_per_100k':'suicides_sum'})\n",
    "#  Recommended from v0.25\n",
    "#  .agg(suicides_sum=('suicides_per_100k': 'sum'))\n",
    ").nlargest(10, columns='suicides_sum')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">suicides_no</th>\n",
       "      <th colspan=\"2\" halign=\"left\">population</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sex</th>\n",
       "      <th>female</th>\n",
       "      <th>male</th>\n",
       "      <th>female</th>\n",
       "      <th>male</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>age</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15-24 years</th>\n",
       "      <td>20</td>\n",
       "      <td>79</td>\n",
       "      <td>410136</td>\n",
       "      <td>426957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25-34 years</th>\n",
       "      <td>47</td>\n",
       "      <td>147</td>\n",
       "      <td>537823</td>\n",
       "      <td>530378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35-54 years</th>\n",
       "      <td>124</td>\n",
       "      <td>360</td>\n",
       "      <td>1072711</td>\n",
       "      <td>1094229</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5-14 years</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>412273</td>\n",
       "      <td>436831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55-74 years</th>\n",
       "      <td>128</td>\n",
       "      <td>239</td>\n",
       "      <td>723750</td>\n",
       "      <td>649009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75+ years</th>\n",
       "      <td>79</td>\n",
       "      <td>152</td>\n",
       "      <td>330903</td>\n",
       "      <td>184589</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            suicides_no      population         \n",
       "sex              female male     female     male\n",
       "age                                             \n",
       "15-24 years          20   79     410136   426957\n",
       "25-34 years          47  147     537823   530378\n",
       "35-54 years         124  360    1072711  1094229\n",
       "5-14 years            1    4     412273   436831\n",
       "55-74 years         128  239     723750   649009\n",
       "75+ years            79  152     330903   184589"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(mi_df\n",
    " .loc[('Switzerland', 2000)]\n",
    " .unstack('sex')\n",
    " [['suicides_no', 'population']]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "def log_head(df, head_count=10):\n",
    "    print(df.head(head_count))\n",
    "    return df\n",
    "\n",
    "def log_columns(df):\n",
    "    print(df.columns)\n",
    "    return df\n",
    "\n",
    "def log_shape(df):\n",
    "    print(f'shape = {df.shape}')\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "scrolled": false,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape = (0, 13)\n",
      "CPU times: user 537 ms, sys: 6.77 ms, total: 543 ms\n",
      "Wall time: 548 ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>suicides_no</th>\n",
       "      <th>population</th>\n",
       "      <th>suicides_per_100k</th>\n",
       "      <th>country_year</th>\n",
       "      <th>HDI for year</th>\n",
       "      <th>gdp_year</th>\n",
       "      <th>gdp_capita</th>\n",
       "      <th>generation</th>\n",
       "      <th>valid_cy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [country, year, sex, age, suicides_no, population, suicides_per_100k, country_year, HDI for year, gdp_year, gdp_capita, generation, valid_cy]\n",
       "Index: []"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "# Validation of the `country-year` column.\n",
    "(df\n",
    " .assign(valid_cy=lambda _serie: _serie.apply(\n",
    "     lambda _row: re.split(r'(?=\\d{4})', _row['country_year'])[1] == str(_row['year']),\n",
    "     axis=1))\n",
    " .query('valid_cy == False')\n",
    " .pipe(log_shape)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": false,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape = (0, 9)\n",
      "CPU times: user 2.23 s, sys: 9.79 ms, total: 2.24 s\n",
      "Wall time: 2.25 s\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>suicides_no</th>\n",
       "      <th>population</th>\n",
       "      <th>suicides_per_100k</th>\n",
       "      <th>country_year</th>\n",
       "      <th>HDI for year</th>\n",
       "      <th>gdp_year</th>\n",
       "      <th>gdp_capita</th>\n",
       "      <th>generation</th>\n",
       "      <th>valid_cy</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [suicides_no, population, suicides_per_100k, country_year, HDI for year, gdp_year, gdp_capita, generation, valid_cy]\n",
       "Index: []"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "# Validation of the `country-year` column.\n",
    "(mi_df\n",
    " .assign(valid_cy=lambda _serie: _serie.apply(\n",
    "     lambda _row: re.split(r'(?=\\d{4})', _row['country_year'])[1] == str(_row.name[1]),\n",
    "     axis=1))\n",
    " .query('valid_cy == False')\n",
    " .pipe(log_shape)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape = (27820, 12)\n",
      "shape = (2321, 1)\n",
      "CPU times: user 9.39 ms, sys: 2.09 ms, total: 11.5 ms\n",
      "Wall time: 10.4 ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>sum_suicides_per_100k_female</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2009</th>\n",
       "      <th>Republic of Korea</th>\n",
       "      <td>170.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1989</th>\n",
       "      <th>Singapore</th>\n",
       "      <td>163.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1986</th>\n",
       "      <th>Singapore</th>\n",
       "      <td>161.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010</th>\n",
       "      <th>Republic of Korea</th>\n",
       "      <td>158.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007</th>\n",
       "      <th>Republic of Korea</th>\n",
       "      <td>149.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011</th>\n",
       "      <th>Republic of Korea</th>\n",
       "      <td>147.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1991</th>\n",
       "      <th>Hungary</th>\n",
       "      <td>147.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <th>Republic of Korea</th>\n",
       "      <td>147.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000</th>\n",
       "      <th>Aruba</th>\n",
       "      <td>146.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005</th>\n",
       "      <th>Republic of Korea</th>\n",
       "      <td>145.35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        sum_suicides_per_100k_female\n",
       "year country                                        \n",
       "2009 Republic of Korea                        170.89\n",
       "1989 Singapore                                163.16\n",
       "1986 Singapore                                161.67\n",
       "2010 Republic of Korea                        158.52\n",
       "2007 Republic of Korea                        149.60\n",
       "2011 Republic of Korea                        147.84\n",
       "1991 Hungary                                  147.35\n",
       "2008 Republic of Korea                        147.04\n",
       "2000 Aruba                                    146.22\n",
       "2005 Republic of Korea                        145.35"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "# which year, per country, had the most suicides among females\n",
    "(df\n",
    " .pipe(log_shape)\n",
    " .query('sex == \"female\"')\n",
    " .groupby(['year', 'country'])\n",
    " .agg({'suicides_per_100k':'sum'})\n",
    " .pipe(log_shape)\n",
    " .rename(columns={'suicides_per_100k':'sum_suicides_per_100k_female'})\n",
    "#  Recommended from v0.25\n",
    "#  .agg(sum_suicides_per_100k_female=('suicides_per_100k': 'sum'))\n",
    " .nlargest(n=10, columns=['sum_suicides_per_100k_female'])\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 11.4 ms, sys: 1.25 ms, total: 12.6 ms\n",
      "Wall time: 12 ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>sum_suicides_per_100k_female</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2009</th>\n",
       "      <th>Republic of Korea</th>\n",
       "      <td>170.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1989</th>\n",
       "      <th>Singapore</th>\n",
       "      <td>163.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1986</th>\n",
       "      <th>Singapore</th>\n",
       "      <td>161.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010</th>\n",
       "      <th>Republic of Korea</th>\n",
       "      <td>158.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007</th>\n",
       "      <th>Republic of Korea</th>\n",
       "      <td>149.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011</th>\n",
       "      <th>Republic of Korea</th>\n",
       "      <td>147.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1991</th>\n",
       "      <th>Hungary</th>\n",
       "      <td>147.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <th>Republic of Korea</th>\n",
       "      <td>147.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000</th>\n",
       "      <th>Aruba</th>\n",
       "      <td>146.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005</th>\n",
       "      <th>Republic of Korea</th>\n",
       "      <td>145.35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        sum_suicides_per_100k_female\n",
       "year country                                        \n",
       "2009 Republic of Korea                        170.89\n",
       "1989 Singapore                                163.16\n",
       "1986 Singapore                                161.67\n",
       "2010 Republic of Korea                        158.52\n",
       "2007 Republic of Korea                        149.60\n",
       "2011 Republic of Korea                        147.84\n",
       "1991 Hungary                                  147.35\n",
       "2008 Republic of Korea                        147.04\n",
       "2000 Aruba                                    146.22\n",
       "2005 Republic of Korea                        145.35"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "# which year, per country, had the most suicides among females\n",
    "(mi_df\n",
    " .query('sex == \"female\"')\n",
    " .groupby(['year', 'country'])\n",
    " .agg({'suicides_per_100k':'sum'})\n",
    " .rename(columns={'suicides_per_100k':'sum_suicides_per_100k_female'})\n",
    "#  Recommended from v0.25\n",
    "#  .agg(sum_suicides_per_100k_female=('suicides_per_100k': 'sum'))\n",
    " .nlargest(n=10, columns=['sum_suicides_per_100k_female'])\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "from sklearn.preprocessing import MinMaxScaler\n",
    "\n",
    "def norm_df(df, columns):\n",
    "    return df.assign(**{col: MinMaxScaler().fit_transform(df[[col]].values.astype(float)) \n",
    "                        for col in columns})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "male\n",
      "                       suicides_per_100k_sum  gdp_year_mean\n",
      "suicides_per_100k_sum               1.000000       0.421218\n",
      "gdp_year_mean                       0.421218       1.000000\n",
      "\n",
      "\n",
      "female\n",
      "                       suicides_per_100k_sum  gdp_year_mean\n",
      "suicides_per_100k_sum               1.000000       0.452343\n",
      "gdp_year_mean                       0.452343       1.000000\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# decrease in gdp correlated with increase in suicide number (is it gender related?)\n",
    "for sex in ['male', 'female']:\n",
    "    print(sex)\n",
    "    print(\n",
    "        df\n",
    "        .query(f'sex == \"{sex}\"')\n",
    "        .groupby(['country'])\n",
    "        .agg({'suicides_per_100k': 'sum', 'gdp_year': 'mean'})\n",
    "        .rename(columns={'suicides_per_100k':'suicides_per_100k_sum', \n",
    "                         'gdp_year': 'gdp_year_mean'})\n",
    "#         Recommended in v0.25\n",
    "#         .agg(suicides_per_100k=('suicides_per_100k_sum', 'sum'), \n",
    "#              gdp_year=('gdp_year_mean', 'mean'))\n",
    "        .pipe(norm_df, columns=['suicides_per_100k_sum', 'gdp_year_mean'])\n",
    "        .corr(method='spearman')\n",
    "    )\n",
    "    print('\\n')\n",
    "        "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Random tips"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1.8 s, sys: 7.56 ms, total: 1.8 s\n",
      "Wall time: 1.81 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "for row in df.iterrows(): continue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 45.3 ms, sys: 1.45 ms, total: 46.7 ms\n",
      "Wall time: 46.1 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "for tup in df.itertuples(): continue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'tuple'>\n"
     ]
    }
   ],
   "source": [
    "for tup in df.itertuples(): print(type(row));break"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
